﻿-- =============================================
-- Author:		Stocksdale, Jon
-- Create date: June 21, 2016
-- Description:	Traverse Related Entities
-- =============================================
CREATE PROCEDURE FetchRelatedEntities
	-- Add the parameters for the stored procedure here
	@LEVELS int = 2, 
	@STARTINGENTITYID nvarchar(100),
	@Entity1Type nvarchar(255) = null,
	@SRNameWithDir nvarchar(100) = null,
	-- Optionally limits the search to one particular relationship and direction
	@ReadDirect int=null,
	@ReadChild int=null,
	@WriteDirect int=null,
	@WriteChild int=null,
	@SD datetime = null,
	@ED datetime = null,
	@TD nvarchar(100) = '',
	@FunctionsOnly bit = 0,
	@SelfFunctions bit = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	CREATE TABLE #TEMPRLIST (
		DisplayFunctionName nvarchar(100),
		ShortFunctionName nvarchar(100),
		ViewName nvarchar(100),
		FunctionCommand nvarchar(100),
		E1LinkName nvarchar(100),
		E2LinkName nvarchar(100),
		TabOrder int,
		Entity1ID nvarchar(100),
		Entity1Type nvarchar(100),
		Entity1Name nvarchar(255),
		SRNameWithDir nvarchar(100),
		RelationshipName nvarchar(50),
		SuperRelationship nvarchar(20),
		Entity2ID nvarchar(100), 
		Entity2Type nvarchar(100),
		Entity2Name nvarchar(255),
		StartDate datetime, 
		EndDate datetime,
		E1_ReadDirect_E2 int,
		E1_ReadChild_E2 int,
		E1_WriteDirect_E2 int,
		E1_WriteChild_E2 int
	)
	CREATE TABLE #Temp (
		DisplayFunctionName nvarchar(100),
		ShortFunctionName nvarchar(100),
		ViewName nvarchar(100),
		FunctionCommand nvarchar(100),
		E1LinkName nvarchar(100),
		E2LinkName nvarchar(100),
		TabOrder int,
		Entity1ID nvarchar(100),
		Entity1Type nvarchar(100),
		Entity1Name nvarchar(255),
		SRNameWithDir nvarchar(100),
		RelationshipName nvarchar(50),
		SuperRelationship nvarchar(20),
		Entity2ID nvarchar(100), 
		Entity2Type nvarchar(100),
		Entity2Name nvarchar(255),
		StartDate datetime, 
		EndDate datetime,
		E1_ReadDirect_E2 int,
		E1_ReadChild_E2 int,
		E1_WriteDirect_E2 int,
		E1_WriteChild_E2 int
	)
	CREATE TABLE #Temp2 (
		DisplayFunctionName nvarchar(100),
		ShortFunctionName nvarchar(100),
		ViewName nvarchar(100),
		FunctionCommand nvarchar(100),
		E1LinkName nvarchar(100),
		E2LinkName nvarchar(100),
		TabOrder int,
		Entity1ID nvarchar(100),
		Entity1Type nvarchar(100),
		Entity1Name nvarchar(255),
		SRNameWithDir nvarchar(100),
		RelationshipName nvarchar(50),
		SuperRelationship nvarchar(20),
		Entity2ID nvarchar(100), 
		Entity2Type nvarchar(100),
		Entity2Name nvarchar(255),
		StartDate datetime, 
		EndDate datetime,
		E1_ReadDirect_E2 int,
		E1_ReadChild_E2 int,
		E1_WriteDirect_E2 int,
		E1_WriteChild_E2 int
	)
	CREATE TABLE #EXISTINGIDS (
		EntityID nvarchar(100)
	)
INSERT INTO #EXISTINGIDS(EntityID) SELECT @STARTINGENTITYID
DECLARE @NEXTID nvarchar(100), @NEXTTYPE nvarchar(100), @RD int, @RC int, @WD int, @WC int, @TRAVERSED nvarchar(100)
INSERT INTO #TEMP EXEC ReadRelatedEntities @Entity1ID = @STARTINGENTITYID, @Entity1Type=@Entity1Type, @TD=@TD, @SELFFUNCTIONS=1
WHILE @LEVELS > 0 BEGIN
	DECLARE RCursor CURSOR FOR
		SELECT DISTINCT Entity2ID, Entity2Type, E1_ReadDirect_E2, E1_ReadChild_E2, E1_WriteDirect_E2, E1_WriteChild_E2
		FROM #Temp
	OPEN RCursor
	FETCH NEXT FROM RCursor INTO @NEXTID, @NEXTTYPE, @RD, @RC, @WD, @WC
	WHILE @@FETCH_STATUS=0 BEGIN
		SET @TRAVERSED = NULL
		SELECT @TRAVERSED=EntityID FROM #EXISTINGIDS WHERE EntityID=@NEXTID
		IF @TRAVERSED IS NULL BEGIN
			INSERT INTO #Temp2 EXEC ReadRelatedEntities @Entity1ID = @NEXTID, @Entity1Type=@NEXTTYPE, @TD=@TD, @ReadDirect=@RD,
				@ReadChild=@RC, @WriteDirect=@WD, @WriteChild=@WC
			INSERT INTO #EXISTINGIDS(EntityID) SELECT @NEXTID
		END
		FETCH NEXT FROM RCursor INTO  @NEXTID, @NEXTTYPE, @RD, @RC, @WD, @WC
	END	
	CLOSE RCursor
	DEALLOCATE RCursor
	INSERT INTO #TEMPRLIST SELECT * FROM #Temp
	DELETE FROM #Temp
	INSERT INTO #Temp SELECT * FROM #Temp2
	DELETE FROM #Temp2
	SET @LEVELS = @LEVELS - 1
END
INSERT INTO #TEMPRLIST SELECT * FROM #Temp
SELECT * FROM #TEMPRLIST
DROP TABLE #Temp
DROP TABLE #Temp2
DROP TABLE #TEMPRLIST
END